package com.homework.ch01;

/**
 * @author yuxi
 * @version 1.0
 * @date 2025/11/5
 * @Description:
 */

import java.io.FileOutputStream;
import java.io.ObjectOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
class SimpleJdbcDemo {
    // 已添加字符集配置的URL
    private static String url = "jdbc:mysql://localhost:3306/briup?characterEncoding=utf8&useUnicode=true";
    private static String username = "briup";
    private static String password = "briup";

    public static void main(String[] args) {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);

            insertData(conn);
            List<Customer> list = queryAndConvert(conn);
            System.out.println("2000年前客户：");
            list.forEach(System.out::println);

            serializeList(list, "3-jdbc-demo/src/list.txt");
            System.out.println("序列化完成！");

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try { if (conn != null) conn.close(); } catch (SQLException e) {}
        }
    }

    // 插入数据（无修改，字符集由URL控制）
    private static void insertData(Connection conn) throws SQLException {

        try {
            String sql1 = "insert into t_customer(name,age,birthday) values('zs',18,'1999-01-01')";
            String sql2 = "insert into t_customer(name,age,birthday) values('ls',20,'1997-05-05')";
            String sql3 = "insert into t_customer(name,age,birthday) values('ww',19,'2003-08-08')";
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql1);
            stmt.executeUpdate(sql2);
            stmt.executeUpdate(sql3);
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static List<Customer> queryAndConvert(Connection conn) throws SQLException {
        List<Customer> list = new ArrayList<>();
        String sql = "SELECT id, name, age, birthday FROM t_customer WHERE birthday < '2000-01-01'";

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Customer c = new Customer();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setAge(rs.getInt("age"));
                c.setBirthday(rs.getDate("birthday"));
                list.add(c);
            }
        }
        return list;
    }

    private static void serializeList(List<Customer> list, String path) throws Exception {
        try (ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(path))) {
            oos.writeObject(list);
        }
    }
}
